package cn.neusoft.dao.impl;

import cn.neusoft.bean.Business;
import cn.neusoft.dao.IBusinessDao;
import cn.neusoft.query.BaseQuery;
import cn.neusoft.query.BusinessQuery;
import cn.neusoft.utils.JdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BusinessDao implements IBusinessDao {

    private QueryRunner queryRunner;

    public BusinessDao(){
        queryRunner = JdbcUtils.getQueryRunner();
    }

    @Override
    public int add(Business business) throws SQLException {
        String sql = "insert into business(businessAccount, businessName, businessAddress, businessExplain, businessImg, startPrice, deliveryPrice, remarks) VALUES(?,?,?,?,?,?,?,?)";
        return queryRunner.update(sql, business.getBusinessAccount(),
                business.getBusinessName(),
                business.getBusinessAddress(),
                business.getBusinessExplain(),
                business.getBusinessImg(),
                business.getStartPrice(),
                business.getDeliveryPrice(),
                business.getRemarks());
    }

    @Override
    public int update(Business business) throws SQLException {
        String sql = "update business set businessName=?, businessAddress=?, businessExplain=?, businessImg=?, startPrice=?, deliveryPrice=?, remarks=? where businessId=?";
        return queryRunner.update(sql,
                business.getBusinessName(),
                business.getBusinessAddress(),
                business.getBusinessExplain(),
                business.getBusinessImg(),
                business.getStartPrice(),
                business.getDeliveryPrice(),
                business.getRemarks(),
                business.getBusinessId());
    }

    @Override
    public int delete(Integer id) throws SQLException {
        String sql = "delete from business where businessId=?";
        return queryRunner.update(sql,id);
    }

    @Override
    public Business getById(Integer id) throws SQLException {
        String sql = "select businessId, businessAccount, businessPassword, businessName, businessAddress, businessExplain, businessImg, startPrice, deliveryPrice, remarks from business where businessId=?";
        return queryRunner.query(sql, new BeanHandler<Business>(Business.class), id);
    }

    @Override
    public List<Business> getAll() throws SQLException {
        String sql = "select businessId, businessAccount, businessPassword, businessName, businessAddress, businessExplain, businessImg, startPrice, deliveryPrice, remarks from business";
        return queryRunner.query(sql, new BeanListHandler<Business>(Business.class));
    }

    @Override
    public int getCount(BaseQuery baseQuery) throws SQLException {
        //传递的真实类型是BusinessQuery  所以强转  目的是获取搜索条件
        BusinessQuery query = (BusinessQuery)baseQuery;
        //填充?的参数集合  因为sql是动态拼接的  所以?的数量不确定 有需要就加入到集合  不需要则不加入
        List<Object> params = new ArrayList<>();
        String sql = "select count(businessId) from business where 1=1 ";
        if(query.getBusinessName()!=null) {
            sql += " and businessName like concat('%',?,'%')";
            params.add(query.getBusinessName());
        }
        if (query.getBusinessAddress()!=null){
            sql += " and businessAddress like concat('%',?,'%')";
            params.add(query.getBusinessAddress());
        }
        System.out.println("查询总记录："+sql);
        System.out.println("参数集合："+params);

        return queryRunner.query(sql, new ScalarHandler<Long>(), params.toArray()).intValue();
    }

    @Override
    public List<Business> getByPage(BaseQuery baseQuery) throws SQLException {
        //传递的真实类型是BusinessQuery  所以强转  目的是获取搜索条件
        BusinessQuery query = (BusinessQuery)baseQuery;
        //填充?的参数集合  因为sql是动态拼接的  所以?的数量不确定 有需要就加入到集合  不需要则不加入
        List<Object> params = new ArrayList<>();
        //可以动态拼接条件搜索的部分  where 1=1
        String sql = "select businessId, businessAccount, businessPassword, businessName, businessAddress, businessExplain, businessImg, startPrice, deliveryPrice, remarks from business" +
                " where 1=1";
        //动态sql  根据条件参数的传递来确定要不要拼接sql
        if(query.getBusinessName()!=null) {
            sql += " and businessName like concat('%',?,'%')";
            params.add(query.getBusinessName());
        }
        //动态sql  根据条件参数的传递来确定要不要拼接sql
        if (query.getBusinessAddress()!=null){
            sql += " and businessAddress like concat('%',?,'%')";
            params.add(query.getBusinessAddress());
        }
        sql += " ORDER BY businessId DESC limit ?,?";
        //给limit后面的两个?填充的  一直都有
        params.add(query.getStartIndex());
        params.add(query.getPageCount());

        System.out.println("查询集合："+sql);
        System.out.println("参数集合："+params);

        return queryRunner.query(sql, new BeanListHandler<Business>(Business.class), params.toArray());

    }
}
